﻿using Aspose.Cells;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading.Tasks;

namespace MDM
{
    public static class ExcelHelper
    {
        //循环遍历获取excel的中每行每列的值  
        /// <summary>
        /// 将
        /// </summary>
        /// <param name="excel"></param>
        /// <returns></returns>
        public static DataTable GetImportExcelDataTable(string fileName)
        {
            Workbook workbook = new Workbook(fileName);
            Worksheet sheet = workbook.Worksheets[0];
            Cells cells = sheet.Cells;
            //将表中的信息转到datatable
            DataTable table = cells.ExportDataTable(1, 0, cells.MaxRow, 7);
            // Cell a=  cells.LastCell;
            return RemoveEmpty(table);
        }

        /// <summary>
        /// 查询出Excel手机号那一列
        /// </summary>
        /// <param name="fileName"></param>
        /// <returns></returns>
        public static List<string> queryMobile(string fileName)
        {
            Workbook workbook = new Workbook(fileName);
            Worksheet sheet = workbook.Worksheets[0];
            Cells cells = sheet.Cells;
            List<string> list = new List<string>();
            for (int j = 0; j < cells.MaxDataRow + 1; j++)
            {
                string mobile = cells[j, 3].StringValue.Trim();
                if (IsNumeric(mobile))
                {
                    list.Add(mobile);
                }
            }
            return list;
        }


        public static DataTable RemoveEmpty(DataTable dt)
        {
            List<DataRow> removelist = new List<DataRow>();
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                bool IsNull = true;
                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    if (!string.IsNullOrEmpty(dt.Rows[i][j].ToString().Trim()))
                    {
                        IsNull = false;
                    }
                }
                if (IsNull)
                {
                    removelist.Add(dt.Rows[i]);
                }
            }
            for (int i = 0; i < removelist.Count; i++)
            {
                dt.Rows.Remove(removelist[i]);
            }
            return dt;
        }

        /// <summary>
        /// 判断是否是电话号码
        /// </summary>
        /// <param name="value"></param>
        /// <returns></returns>
        public static bool IsNumeric(string value)
        {
            return Regex.IsMatch(value, @"0?(13|14|15|17|18)[0-9]{9}");
        }
    }
}
